package com.lh.tams.dao.base;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.lh.tams.util.Page;

/**
 * 封装dao的基本操作和分页逻辑
 * @author hc24
 *
 */
public class HibernateBaseDao extends HibernateDaoSupport {
	/** 每个table的默认id，这是约定 */
	private static final String TABLE_ID="id_bgt";
	
	/** 添加 */

	public void add(Object object) {

		getHibernateTemplate().save(object);
	}

	/** 更新 */
	public void update(Object object) {

		getHibernateTemplate().update(object);
	}

	/** 删除 */
	@SuppressWarnings("unchecked")
	public void delete(Class c, Serializable id) {

		getHibernateTemplate().delete(getHibernateTemplate().load(c, id));
	}
	
	/** 批量删除 */
	public void delete(final String table,final long[] ids){
		getHibernateTemplate().execute(new HibernateCallback<Object>() {
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Connection con=session.connection();
				StringBuilder delSql=new StringBuilder();
				delSql.append("delete from "+table+" where "+TABLE_ID+" in(");
				for(int i=0;i<ids.length-1;i++){
					delSql.append("?,");
				}
				delSql.append("?)");
				PreparedStatement ps=con.prepareStatement(delSql.toString());
				for(int i=0;i<ids.length;i++){
					ps.setLong(i+1, ids[i]);
				}
				ps.executeUpdate();
				return null;
			}
			
		});
	}
	
	//根据案件ID删除相关表数据
	public void deleteByCaseId(final String table,final long id){
		getHibernateTemplate().execute(new HibernateCallback<Object>() {
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Connection con=session.connection();
				StringBuilder delSql=new StringBuilder();
				delSql.append("delete from "+table+" where case_id="+id);
				PreparedStatement ps=con.prepareStatement(delSql.toString());
				ps.executeUpdate();
				return null;
			}
			
		});
	}
	
	//根据案件编号删除相关表数据
	public void deleteByCaseNo(final String table,final String caseNo){
		getHibernateTemplate().execute(new HibernateCallback<Object>() {
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				Connection con=session.connection();
				StringBuilder delSql=new StringBuilder();
				delSql.append("delete from "+table+" where case_no='"+caseNo+"'");
				PreparedStatement ps=con.prepareStatement(delSql.toString());
				ps.executeUpdate();
				return null;
			}
			
		});
	}

	/**
	 * 根据Hql语句查得数据库记录的总数
	 * 
	 * @param hql
	 * @param args
	 * @return
	 */
	public int getTotalCount(String hql, Object[] args) {
		Session session = getSession();
		Query query = session.createQuery(hql);
		if (args != null) {
			for (int i = 0; i < args.length; i++)
				query.setParameter(i, args[i]);
		}
		return ((Long) query.uniqueResult()).intValue();
	}

	/**
	 * 
	 * @param hql 带?号的查询hql语句
	 * @param args ?号对应的参数数组
	 * @param pageNo 页码
	 * @param pageSize 分页大小
	 * @return 返回{@link Page} 封装数据和总数
	 */
	public Page queryForList(String hql,Object[] args, int pageNo, int pageSize) {
		
		int fromPoint=hql.indexOf("from");
		int orderPoint=hql.indexOf("order by");
		if(orderPoint==-1)
			orderPoint=hql.length();
		String queryCountHql="select count(*) "+hql.substring(fromPoint,orderPoint);
		int totalCount = getTotalCount(queryCountHql, args);
		Query query = getSession().createQuery(hql);
		if (args != null) {
			for (int i = 0; i < args.length; i++)
				query.setParameter(i, args[i]);
		}
		int startNo=Page.getStartOfPage(pageNo, pageSize);//起始号(包含)
		List data = query.setFirstResult(startNo).setMaxResults(pageSize).list();
		Page page=new Page(data,startNo,totalCount,pageSize);		
		return page;
	}

	
	/**
	 * 根据Sql语句查得数据库记录的总数
	 * 
	 * @param sql
	 * @param args
	 * @return
	 */
	public int getTotalCountBySql(String sql, Object[] args) {
		Session session = getSession();
		Query query = session.createSQLQuery(sql);
		if (args != null) {
			for (int i = 0; i < args.length; i++)
				query.setParameter(i, args[i]);
		}
		return ((Integer) query.uniqueResult()).intValue();
	}
	
	/**
	 * 
	 * @param sql 带?号的查询sql语句 
	 * @param args ?号对应的参数数组
	 * @param pageNo 页码
	 * @param pageSize 分页大小
	 * @return 返回{@link Page} 封装数据和总数
	 */
	public Page queryForArray(String sql,Object[] args, int pageNo, int pageSize) {
		
		int orderPoint=sql.indexOf("order by");
		if(orderPoint==-1)
			orderPoint=sql.length();
		String queryCountSql="select count(*) from ( "+sql.substring(0,orderPoint)+" ) t";
		int totalCount = getTotalCountBySql(queryCountSql, args);
		SQLQuery query = this.getSession().createSQLQuery(sql);
		if (args != null) {
			for (int i = 0; i < args.length; i++)
				query.setParameter(i, args[i]);
		}
		int startNo=Page.getStartOfPage(pageNo, pageSize);//起始号(包含)
		//查询数据
		List data = query.setFirstResult(startNo).setMaxResults(pageSize).list();
		Page page=new Page(data,startNo,totalCount,pageSize);		
		return page;
	}
	
}
